<!DOCTYPE html>



  


<html class="theme-next pisces use-motion" lang="zh-Hans">
<head>
  <!-- hexo-inject:begin --><!-- hexo-inject:end --><meta charset="UTF-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1"/>
<meta name="theme-color" content="#222">









<meta http-equiv="Cache-Control" content="no-transform" />
<meta http-equiv="Cache-Control" content="no-siteapp" />
















  
  
  <link href="/lib/fancybox/source/jquery.fancybox.css?v=2.1.5" rel="stylesheet" type="text/css" />







<link href="/lib/font-awesome/css/font-awesome.min.css?v=4.6.2" rel="stylesheet" type="text/css" />

<link href="/css/main.css?v=5.1.4" rel="stylesheet" type="text/css" />


  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png?v=5.1.4">


  <link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32-next.png?v=5.1.4">


  <link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16-next.png?v=5.1.4">


  <link rel="mask-icon" href="/images/logo.svg?v=5.1.4" color="#222">





  <meta name="keywords" content="etl tool," />










<meta name="description" content="Hive入门讲解Hive定位：ETL（数据仓库）工具 将数据从来源端经过抽取（extract）、转换（transform）、加载（load）至目的端的工具,如像：kettle     DML批量插入/批量导入12LOAD DATA [LOCAL] INPATH &amp;apos;filepath&amp;apos; [OVERWRITE] INTO TABLE tablename [PARTITION (par">
<meta name="keywords" content="etl tool">
<meta property="og:type" content="article">
<meta property="og:title" content="Hive入门讲解">
<meta property="og:url" content="http://yoursite.com/2017/02/10/Hive2/index.html">
<meta property="og:site_name" content="Frone&#39;s Blog">
<meta property="og:description" content="Hive入门讲解Hive定位：ETL（数据仓库）工具 将数据从来源端经过抽取（extract）、转换（transform）、加载（load）至目的端的工具,如像：kettle     DML批量插入/批量导入12LOAD DATA [LOCAL] INPATH &amp;apos;filepath&amp;apos; [OVERWRITE] INTO TABLE tablename [PARTITION (par">
<meta property="og:locale" content="zh-Hans">
<meta property="og:updated_time" content="2018-07-13T11:06:08.757Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="Hive入门讲解">
<meta name="twitter:description" content="Hive入门讲解Hive定位：ETL（数据仓库）工具 将数据从来源端经过抽取（extract）、转换（transform）、加载（load）至目的端的工具,如像：kettle     DML批量插入/批量导入12LOAD DATA [LOCAL] INPATH &amp;apos;filepath&amp;apos; [OVERWRITE] INTO TABLE tablename [PARTITION (par">



<script type="text/javascript" id="hexo.configurations">
  var NexT = window.NexT || {};
  var CONFIG = {
    root: '/',
    scheme: 'Pisces',
    version: '5.1.4',
    sidebar: {"position":"left","display":"post","offset":12,"b2t":false,"scrollpercent":false,"onmobile":false},
    fancybox: true,
    tabs: true,
    motion: {"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},
    duoshuo: {
      userId: '0',
      author: '博主'
    },
    algolia: {
      applicationID: '',
      apiKey: '',
      indexName: '',
      hits: {"per_page":10},
      labels: {"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}
    }
  };
</script>



  <link rel="canonical" href="http://yoursite.com/2017/02/10/Hive2/"/>





  <title>Hive入门讲解 | Frone's Blog</title><!-- hexo-inject:begin --><!-- hexo-inject:end -->
  








</head>

<body itemscope itemtype="http://schema.org/WebPage" lang="zh-Hans">

  
  
    
  

  <!-- hexo-inject:begin --><!-- hexo-inject:end --><div class="container sidebar-position-left page-post-detail">
    <div class="headband"></div>

    <header id="header" class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-wrapper">
  <div class="site-meta ">
    

    <div class="custom-logo-site-title">
      <a href="/"  class="brand" rel="start">
        <span class="logo-line-before"><i></i></span>
        <span class="site-title">Frone's Blog</span>
        <span class="logo-line-after"><i></i></span>
      </a>
    </div>
      
        <p class="site-subtitle">技术小窝</p>
      
  </div>

  <div class="site-nav-toggle">
    <button>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
    </button>
  </div>
</div>

<nav class="site-nav">
  

  
    <ul id="menu" class="menu">
      
        
        <li class="menu-item menu-item-home">
          <a href="/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-home"></i> <br />
            
            首页
          </a>
        </li>
      
        
        <li class="menu-item menu-item-about">
          <a href="/about/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-user"></i> <br />
            
            关于
          </a>
        </li>
      
        
        <li class="menu-item menu-item-tags">
          <a href="/tags/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-tags"></i> <br />
            
            标签
          </a>
        </li>
      
        
        <li class="menu-item menu-item-categories">
          <a href="/categories/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-th"></i> <br />
            
            分类
          </a>
        </li>
      
        
        <li class="menu-item menu-item-archives">
          <a href="/archives/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-archive"></i> <br />
            
            归档
          </a>
        </li>
      
        
        <li class="menu-item menu-item-commonweal">
          <a href="/404/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-heartbeat"></i> <br />
            
            公益404
          </a>
        </li>
      

      
    </ul>
  

  
</nav>



 </div>
    </header>

    <main id="main" class="main">
      <div class="main-inner">
        <div class="content-wrap">
          <div id="content" class="content">
            

  <div id="posts" class="posts-expand">
    

  

  
  
  

  <article class="post post-type-normal" itemscope itemtype="http://schema.org/Article">
  
  
  
  <div class="post-block">
    <link itemprop="mainEntityOfPage" href="http://yoursite.com/2017/02/10/Hive2/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="name" content="Frone Xie">
      <meta itemprop="description" content="">
      <meta itemprop="image" content="/images/avatar.gif">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Frone's Blog">
    </span>

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">Hive入门讲解</h1>
        

        <div class="post-meta">
          <span class="post-time">
            
              <span class="post-meta-item-icon">
                <i class="fa fa-calendar-o"></i>
              </span>
              
                <span class="post-meta-item-text">发表于</span>
              
              <time title="创建于" itemprop="dateCreated datePublished" datetime="2017-02-10T18:00:00+08:00">
                2017-02-10
              </time>
            

            

            
          </span>

          
            <span class="post-category" >
            
              <span class="post-meta-divider">|</span>
            
              <span class="post-meta-item-icon">
                <i class="fa fa-folder-o"></i>
              </span>
              
                <span class="post-meta-item-text">分类于</span>
              
              
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
                  <a href="/categories/Hive/" itemprop="url" rel="index">
                    <span itemprop="name">Hive</span>
                  </a>
                </span>

                
                
              
            </span>
          

          
            
          

          
          
             <span id="/2017/02/10/Hive2/" class="leancloud_visitors" data-flag-title="Hive入门讲解">
               <span class="post-meta-divider">|</span>
               <span class="post-meta-item-icon">
                 <i class="fa fa-eye"></i>
               </span>
               
                 <span class="post-meta-item-text">阅读次数&#58;</span>
               
                 <span class="leancloud-visitors-count"></span>
             </span>
          

          

          

          

        </div>
      </header>
    

    
    
    
    <div class="post-body" itemprop="articleBody">

      
      

      
        <h1 id="Hive入门讲解"><a href="#Hive入门讲解" class="headerlink" title="Hive入门讲解"></a>Hive入门讲解</h1><p>Hive定位：ETL（数据仓库）工具</p>
<p>将数据从来源端经过抽取（extract）、转换（transform）、加载（load）至目的端的工具,如像：kettle    </p>
<h2 id="DML"><a href="#DML" class="headerlink" title="DML"></a>DML</h2><p><strong>批量插入/批量导入</strong><br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">LOAD DATA [LOCAL] INPATH &apos;filepath&apos; [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]</span><br><span class="line">注：filepath可以是hdfs路径或者是S3路径，如hdfs://namenode:9000/user/hive/project/data1</span><br></pre></td></tr></table></figure></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">1.从本地文件导入到表</span><br><span class="line">load data local inpath &apos;test.txt&apos; into table test;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">2.从hdfs导入到表</span><br><span class="line">load data inpath &apos;/home/test/add.txt&apos; into table test;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">3.从表查询中导入到表</span><br><span class="line">insert into table test select id, name, tel from test;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">4.将查询数据导入到多个表</span><br><span class="line">from source_table</span><br><span class="line">insert into table test select id, name, tel from dest1_table select src.* where src.id &lt; 100</span><br><span class="line">insert into table test select id, name, tel from dest2_table select src.* where src.id &lt; 100</span><br><span class="line">insert into table test select id, name, tel from dest3_table select src.* where src.id &lt; 100;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">5.建表时导入</span><br><span class="line">create table test4 as select id, name, tel from test;</span><br></pre></td></tr></table></figure>
<p><strong>指定分隔符导出数据</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">insert overwrite local directory &apos;/home/hadoop/export_hive&apos; </span><br><span class="line">row format delimited </span><br><span class="line">fields terminated by &apos;\t&apos; </span><br><span class="line">select * from test;</span><br></pre></td></tr></table></figure>
<p><strong>删除/清空</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">1.删除table1中不符合条件的数据</span><br><span class="line">insert overwrite table table1 </span><br><span class="line">select * from table1 where XXXX;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">2.清空表</span><br><span class="line">insert overwrite table t_table1 </span><br><span class="line">select * from t_table1 where 1=0;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">3.截断表（注：不能截断外部表）</span><br><span class="line">truncate table table_name;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">4.删除hdfs对应的表数据达到清空表（表结构依然存在）</span><br><span class="line">hdfs dfs -rmr /user/hive/warehouse/test</span><br><span class="line"></span><br><span class="line">注：1和2本质是覆写表来实现清除数据</span><br></pre></td></tr></table></figure>
<p><strong>delete 与 update</strong></p>
<p>在hive中默认不支持事务，因此默认不支持delete与update，如果需要支持必须在hive-site.xml中配置打开</p>
<h2 id="DDL"><a href="#DDL" class="headerlink" title="DDL"></a>DDL</h2><p>库/表/索引/视图/分区/分桶</p>
<h4 id="数据库"><a href="#数据库" class="headerlink" title="数据库"></a>数据库</h4><p>列出/创建/修改/删除/查看信息</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">1.列出所有数据库</span><br><span class="line">show databases;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">2.创建数据库</span><br><span class="line">create database test;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">3.删除</span><br><span class="line">drop database test;</span><br><span class="line"></span><br><span class="line">处于安全原因，直接drop有数据的数据库会报错，此时需要cascade关键字忽略报错删除</span><br><span class="line">drop database if exists test cascade;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">4.查看数据库信息</span><br><span class="line">describe database test;</span><br></pre></td></tr></table></figure>
<h4 id="表"><a href="#表" class="headerlink" title="表"></a>表</h4><p>列出/创建/修改/删除/查看信息<br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">1.列出所有表</span><br><span class="line"></span><br><span class="line">当前数据库的所有表</span><br><span class="line">show tables;</span><br><span class="line"></span><br><span class="line">指定数据库的所有表</span><br><span class="line">show tables in db_name;</span><br><span class="line"></span><br><span class="line">支持正则</span><br><span class="line">show tables &apos;.*s&apos;;</span><br></pre></td></tr></table></figure></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">2.创建表</span><br><span class="line">create table test</span><br><span class="line">(id int,</span><br><span class="line">a string</span><br><span class="line">)</span><br><span class="line">ROW FORMAT DELIMITED        行分割</span><br><span class="line">FIELDS TERMINATED BY ‘,’    字段分隔符</span><br><span class="line">LINES TERMINATED BY ‘\n’    行分隔符</span><br><span class="line">STORED AS TEXTFILE;         作为文本存储</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line">创建基于正则切分行字段的表</span><br><span class="line">add jar ../build/contrib/hive_contrib.jar;</span><br><span class="line"></span><br><span class="line">CREATE TABLE apachelog (</span><br><span class="line">host STRING,</span><br><span class="line">identity STRING,</span><br><span class="line">user STRING,</span><br><span class="line">time STRING,</span><br><span class="line">request STRING,</span><br><span class="line">status STRING,</span><br><span class="line">size STRING,</span><br><span class="line">referer STRING,</span><br><span class="line">agent STRING)</span><br><span class="line">ROW FORMAT SERDE &apos;org.apache.hadoop.hive.contrib.serde2.RegexSerDe&apos;</span><br><span class="line">WITH SERDEPROPERTIES (</span><br><span class="line">&quot;input.regex&quot; = &quot;([^ ]*) ([^ ]*) ([^ ]*) (-|//[[^//]]*//]) ([^ /&quot;]*|/&quot;[^/&quot;]*/&quot;) (-|[0-9]*) (-|[0-9]*)(?: ([^ /&quot;]*|/&quot;[^/&quot;]*/&quot;) ([^ /&quot;]*|/&quot;[^/&quot;]*/&quot;))?&quot;,</span><br><span class="line">&quot;output.format.string&quot; = &quot;%1$s %2$s %3$s %4$s %5$s %6$s %7$s %8$s %9$s&quot;</span><br><span class="line">)</span><br><span class="line">STORED AS TEXTFILE;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">3.修改</span><br><span class="line">加一个新列</span><br><span class="line">ALTER TABLE test ADD COLUMNS (new_col2 INT COMMENT &apos;a comment&apos;);</span><br><span class="line"></span><br><span class="line">改表名</span><br><span class="line">ALTER TABLE old_name RENAME TO new_name;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">4.删除</span><br><span class="line">drop table test;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">5.查看信息</span><br><span class="line"></span><br><span class="line">显示列信息</span><br><span class="line">desc test;</span><br><span class="line"></span><br><span class="line">显示详细表信息</span><br><span class="line">desc formatted test;</span><br></pre></td></tr></table></figure>
<h4 id="索引"><a href="#索引" class="headerlink" title="索引"></a>索引</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line">创建索引</span><br><span class="line">CREATE INDEX index_name   </span><br><span class="line">ON TABLE base_table_name (col_name, ...)  </span><br><span class="line">AS &apos;index.handler.class.name&apos;</span><br><span class="line"></span><br><span class="line">如：DROP INDEX index_name ON table_name  </span><br><span class="line"></span><br><span class="line">重建索引</span><br><span class="line">ALTER INDEX index_name ON table_name [PARTITION (...)] REBUILD  </span><br><span class="line"></span><br><span class="line">如：alter index index1_index_test on index_test rebuild; </span><br><span class="line"></span><br><span class="line">删除索引</span><br><span class="line">DROP INDEX index_name ON table_name  </span><br><span class="line"></span><br><span class="line">列出索引</span><br><span class="line">show index on index_test;</span><br></pre></td></tr></table></figure>
<h4 id="视图"><a href="#视图" class="headerlink" title="视图"></a>视图</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">CREATE VIEW [IF NOT EXISTS] view_name [ (column_name [COMMENT column_comment], ...) ][COMMENT view_comment][TBLPROPERTIES (property_name = property_value, ...)] AS SELECT</span><br><span class="line"></span><br><span class="line">注：hive只支持逻辑视图，不支持物化视图</span><br><span class="line">•增加视图</span><br><span class="line">•如果没有提供表名，视图列的名字将由定义的SELECT表达式自动生成</span><br><span class="line">•如果修改基本表的属性，视图中不会体现，无效查询将会失败</span><br><span class="line">•视图是只读的，不能用LOAD/INSERT/ALTER</span><br><span class="line">•删除视图  DROP VIEW view_name</span><br></pre></td></tr></table></figure>
<h4 id="分区（重点）"><a href="#分区（重点）" class="headerlink" title="分区（重点）"></a>分区（重点）</h4><p>列出/创建/修改/删除</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">1.列出一个表的所有分区</span><br><span class="line">show  partitions test;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">2.创建分区表</span><br><span class="line">create table test</span><br><span class="line">(id int,</span><br><span class="line">a string,</span><br><span class="line">)</span><br><span class="line">partitioned by (b string,c int) </span><br><span class="line">ROW FORMAT DELIMITED</span><br><span class="line">FIELDS TERMINATED BY ‘,’ </span><br><span class="line">LINES TERMINATED BY ‘\n’ </span><br><span class="line">STORED AS TEXTFILE;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">3.对现有表添加分区</span><br><span class="line">ALTER TABLE test ADD IF NOT EXISTS</span><br><span class="line">PARTITION (year = 2017) LOCATION ‘/hiveuser/hive/warehouse/data_zh.db/data_zh/2017.txt’;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">4.删除分区</span><br><span class="line">ALTER TABLE test DROP IF EXISTS PARTITION(year =2017);</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">5.加载数据到分区表</span><br><span class="line">LOAD DATA INPATH ‘/data/2017.txt’ INTO TABLE test PARTITION(year=2017);</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">6.未分区表数据导入分区表</span><br><span class="line">insert overwrite table part_table partition (YEAR,MONTH) select * from no_part_table;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">7.动态分区指令</span><br><span class="line">set hive.exec.dynamic.partition=true;</span><br><span class="line">set hive.exec.dynamic.partition.mode=nonstrict;</span><br><span class="line">#set hive.enforce.bucketing = true;</span><br><span class="line"></span><br><span class="line">开启动态分区后导入数据时可以省略指定分区的步骤</span><br><span class="line">LOAD DATA INPATH ‘/data/2017.txt’ INTO TABLE test PARTITION(year);</span><br></pre></td></tr></table></figure>
<h4 id="分桶"><a href="#分桶" class="headerlink" title="分桶"></a>分桶</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">CREATE TABLE bucketed_user (id INT) name STRING)</span><br><span class="line">CLUSTERED BY (id) INTO 4 BUCKETS;</span><br></pre></td></tr></table></figure>
<p>对于每一个表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。</p>
<p>把表（或者分区）组织成桶（Bucket）有两个理由：</p>
<p>（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。</p>
<p>（2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。</p>
<h2 id="DQL"><a href="#DQL" class="headerlink" title="DQL"></a>DQL</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">hive中的order by、distribute by、sort by和cluster by</span><br><span class="line"></span><br><span class="line">order by </span><br><span class="line">全局排序，只有一个Reduce任务</span><br><span class="line"></span><br><span class="line">sort by </span><br><span class="line">只做jubu排序</span><br><span class="line"></span><br><span class="line">distribute by </span><br><span class="line">用distribute by 会对指定的字段按照hashCode值对reduce的个数取模，然后将任务分配到对应的reduce中去执行</span><br><span class="line"></span><br><span class="line">cluster by </span><br><span class="line">distribute by 和 sort by 合用就相当于cluster by，但是cluster by 不能指定排序为asc或 desc 的规则，只能是desc倒序排列</span><br><span class="line"></span><br><span class="line">注：distribute by 和 sort by,distribute by 必须在 sort by前面</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br></pre></td><td class="code"><pre><span class="line">表连接</span><br><span class="line">1.join只支持等值连接</span><br><span class="line"></span><br><span class="line">2.可以 join 多于 2 个表</span><br><span class="line">SELECT a.val, b.val, c.val FROM a JOIN b </span><br><span class="line">    ON (a.key = b.key1) JOIN c ON (c.key = b.key2)</span><br><span class="line">注:如果join中多个表的 join key 是同一个，则 join 会被转化为单个 map/reduce 任务</span><br><span class="line"></span><br><span class="line">3.LEFT，RIGHT和FULL OUTER</span><br><span class="line">SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)</span><br><span class="line"></span><br><span class="line">如果 d 表中找不到对应 c 表的记录，d 表的所有列都会列出 NULL，包括 ds 列。也就是说，join 会过滤 d 表中不能找到匹配 c 表 join key 的所有记录。这样的话，LEFT OUTER 就使得查询结果与 WHERE 子句无关</span><br><span class="line"></span><br><span class="line">•解决办法</span><br><span class="line">　　SELECT c.val, d.val FROM c LEFT OUTER JOIN d </span><br><span class="line">     ON (c.key=d.key AND d.ds=&apos;2009-07-07&apos; AND c.ds=&apos;2009-07-07&apos;)</span><br><span class="line">     </span><br><span class="line">4.LEFT SEMI JOIN</span><br><span class="line">LEFT SEMI JOIN 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行</span><br><span class="line"></span><br><span class="line">SELECT a.key, a.value </span><br><span class="line">  FROM a </span><br><span class="line">  WHERE a.key in </span><br><span class="line">   (SELECT b.key </span><br><span class="line">    FROM B);</span><br><span class="line"></span><br><span class="line">可以被重写为：</span><br><span class="line"></span><br><span class="line">SELECT a.key, a.val </span><br><span class="line">   FROM a LEFT SEMI JOIN b on (a.key = b.key)</span><br><span class="line">   </span><br><span class="line">5.UNION ALL</span><br><span class="line">用来合并多个select的查询结果，需要保证select中字段须一致</span><br><span class="line">　　select_statement UNION ALL select_statement UNION ALL select_statement ...</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">分析窗口函数</span><br><span class="line">over (partition by * order by d desc)</span><br></pre></td></tr></table></figure>
<p>例子：<a href="//www.tuicool.com/articles/zuiIVbZ">Hive分析窗口函数(一) SUM,AVG,MIN,MAX</a><br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">其他</span><br><span class="line">1.不支持EXIST ,NOT EXIST</span><br><span class="line"></span><br><span class="line">2.Top N 关键字 为Limit</span><br><span class="line"></span><br><span class="line">3.支持正则筛选字段，如SELECT `(ds|hr)?+.+` FROM test</span><br></pre></td></tr></table></figure></p>
<h2 id="DCL"><a href="#DCL" class="headerlink" title="DCL"></a>DCL</h2><p>权限</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br></pre></td><td class="code"><pre><span class="line">Grant/revoke语法：</span><br><span class="line">grant/revoke priv_type[column_list] on object_type object to/from principal_type principal_name</span><br><span class="line"></span><br><span class="line">查看grant 定义：</span><br><span class="line">show grant user user_name on table table_name;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">创建/删除角色：</span><br><span class="line">Create/drop Role role_name</span><br><span class="line"></span><br><span class="line">角色分配/回收：</span><br><span class="line">Grant role role_name to user user_name</span><br><span class="line">Revoke role role_name from user user_name</span><br><span class="line"></span><br><span class="line">角色授权：</span><br><span class="line">Grant/revoke priv_type[col_List] on object_type object from/to role role_name</span><br><span class="line"></span><br><span class="line">查看role定义：</span><br><span class="line">show role grant role role_name</span><br><span class="line"></span><br><span class="line">相关hive元信息表：</span><br><span class="line">Db_privs:记录了User/Role在DB上的权限</span><br><span class="line">Tbl_privs:记录了User/Role在table上的权限</span><br><span class="line">Tbl_col_privs：记录了User/Role在table column上的权限</span><br><span class="line">Roles：记录了全部创建的role</span><br><span class="line">Role_map：记录了User与Role的相应关系</span><br></pre></td></tr></table></figure>
<h2 id="Hive函数"><a href="#Hive函数" class="headerlink" title="Hive函数"></a>Hive函数</h2><p>UDF、UDAF和UDTF</p>
<p>UDF实现输入一行输出一行行的函数</p>
<p>UDAF实现输入多行输出一行的函数</p>
<p>UDTF实现输入一行输出多行的函数</p>
<p>自定义UDF、UDAF和UDTF</p>
<h2 id="相关资料"><a href="#相关资料" class="headerlink" title="相关资料"></a>相关资料</h2><p>hive2.0函数大全<br><a href="http://www.cnblogs.com/MOBIN/p/5618747.html" target="_blank" rel="noopener">http://www.cnblogs.com/MOBIN/p/5618747.html</a></p>
<h2 id="Mysql数据导入Hive"><a href="#Mysql数据导入Hive" class="headerlink" title="Mysql数据导入Hive"></a>Mysql数据导入Hive</h2><p>通过sqoop把mysql数据导入到hive(若hive没对应表则自动建表)<br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">./bin/sqoop import --connect jdbc:mysql://172.17.4.1:3306/poi --username lizt  --table poi_amap --hive-import --password 123456</span><br></pre></td></tr></table></figure></p>
<h2 id="python-客户端"><a href="#python-客户端" class="headerlink" title="python 客户端"></a>python 客户端</h2><p><a href="https://github.com/BradRuderman/pyhs2" target="_blank" rel="noopener">pyhs2</a><br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">pip install pyhs2</span><br></pre></td></tr></table></figure></p>

      
    </div>
    
    
    

    
      <div>
        <div id="wechat_subscriber" style="display: block; padding: 10px 0; margin: 20px auto; width: 100%; text-align: center">
    <img id="wechat_subscriber_qcode" src="/uploads/wechat-qcode.jpg" alt="Frone Xie wechat" style="width: 200px; max-width: 100%;"/>
    <div>欢迎关注我的微信公众号！</div>
</div>

      </div>
    

    

    

    <footer class="post-footer">
      
        <div class="post-tags">
          
            <a href="/tags/etl-tool/" rel="tag"># etl tool</a>
          
        </div>
      

      
      
        <div class="post-widgets">
        

        

        
          
          <div id="needsharebutton-postbottom">
            <span class="btn">
              <i class="fa fa-share-alt" aria-hidden="true"></i>
            </span>
          </div>
        
        </div>
      
      

      
        <div class="post-nav">
          <div class="post-nav-next post-nav-item">
            
              <a href="/2016/10/24/HEXO 安装使用说明/" rel="next" title="HEXO 安装使用说明">
                <i class="fa fa-chevron-left"></i> HEXO 安装使用说明
              </a>
            
          </div>

          <span class="post-nav-divider"></span>

          <div class="post-nav-prev post-nav-item">
            
              <a href="/2017/02/10/Hive3/" rel="prev" title="Hive优化以及数据倾斜问题">
                Hive优化以及数据倾斜问题 <i class="fa fa-chevron-right"></i>
              </a>
            
          </div>
        </div>
      

      
      
    </footer>
  </div>
  
  
  
  </article>



    <div class="post-spread">
      
    </div>
  </div>


          </div>
          


          

  



        </div>
        
          
  
  <div class="sidebar-toggle">
    <div class="sidebar-toggle-line-wrap">
      <span class="sidebar-toggle-line sidebar-toggle-line-first"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-last"></span>
    </div>
  </div>

  <aside id="sidebar" class="sidebar">
    
    <div class="sidebar-inner">

      

      
        <ul class="sidebar-nav motion-element">
          <li class="sidebar-nav-toc sidebar-nav-active" data-target="post-toc-wrap">
            文章目录
          </li>
          <li class="sidebar-nav-overview" data-target="site-overview-wrap">
            站点概览
          </li>
        </ul>
      

      <section class="site-overview-wrap sidebar-panel">
        <div class="site-overview">
          <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
            
              <p class="site-author-name" itemprop="name">Frone Xie</p>
              <p class="site-description motion-element" itemprop="description"></p>
          </div>

          <nav class="site-state motion-element">

            
              <div class="site-state-item site-state-posts">
              
                <a href="/archives/">
              
                  <span class="site-state-item-count">29</span>
                  <span class="site-state-item-name">日志</span>
                </a>
              </div>
            

            
              
              
              <div class="site-state-item site-state-categories">
                <a href="/categories/index.html">
                  <span class="site-state-item-count">10</span>
                  <span class="site-state-item-name">分类</span>
                </a>
              </div>
            

            
              
              
              <div class="site-state-item site-state-tags">
                <a href="/tags/index.html">
                  <span class="site-state-item-count">45</span>
                  <span class="site-state-item-name">标签</span>
                </a>
              </div>
            

          </nav>

          

          
            <div class="links-of-author motion-element">
                
                  <span class="links-of-author-item">
                    <a href="https://github.com/frone" target="_blank" title="GitHub">
                      
                        <i class="fa fa-fw fa-github"></i>GitHub</a>
                  </span>
                
                  <span class="links-of-author-item">
                    <a href="https://blog.csdn.net/frone" target="_blank" title="CSDN">
                      
                        <i class="fa fa-fw fa-CSDN"></i>CSDN</a>
                  </span>
                
            </div>
          

          
          

          
          

          

        </div>
      </section>

      
      <!--noindex-->
        <section class="post-toc-wrap motion-element sidebar-panel sidebar-panel-active">
          <div class="post-toc">

            
              
            

            
              <div class="post-toc-content"><ol class="nav"><li class="nav-item nav-level-1"><a class="nav-link" href="#Hive入门讲解"><span class="nav-number">1.</span> <span class="nav-text">Hive入门讲解</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#DML"><span class="nav-number">1.1.</span> <span class="nav-text">DML</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#DDL"><span class="nav-number">1.2.</span> <span class="nav-text">DDL</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#数据库"><span class="nav-number">1.2.0.1.</span> <span class="nav-text">数据库</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#表"><span class="nav-number">1.2.0.2.</span> <span class="nav-text">表</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#索引"><span class="nav-number">1.2.0.3.</span> <span class="nav-text">索引</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#视图"><span class="nav-number">1.2.0.4.</span> <span class="nav-text">视图</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#分区（重点）"><span class="nav-number">1.2.0.5.</span> <span class="nav-text">分区（重点）</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#分桶"><span class="nav-number">1.2.0.6.</span> <span class="nav-text">分桶</span></a></li></ol></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#DQL"><span class="nav-number">1.3.</span> <span class="nav-text">DQL</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#DCL"><span class="nav-number">1.4.</span> <span class="nav-text">DCL</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#Hive函数"><span class="nav-number">1.5.</span> <span class="nav-text">Hive函数</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#相关资料"><span class="nav-number">1.6.</span> <span class="nav-text">相关资料</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#Mysql数据导入Hive"><span class="nav-number">1.7.</span> <span class="nav-text">Mysql数据导入Hive</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#python-客户端"><span class="nav-number">1.8.</span> <span class="nav-text">python 客户端</span></a></li></ol></li></ol></div>
            

          </div>
        </section>
      <!--/noindex-->
      

      

    </div>
  </aside>


        
      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="footer-inner">
        <div class="copyright">&copy; <span itemprop="copyrightYear">2019</span>
  <span class="with-love">
    <i class="fa fa-user"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">Frone Xie</span>

  
</div>


  <div class="powered-by">由 <a class="theme-link" target="_blank" href="https://hexo.io">Hexo</a> 强力驱动</div>



  <span class="post-meta-divider">|</span>



  <div class="theme-info">主题 &mdash; <a class="theme-link" target="_blank" href="https://github.com/iissnan/hexo-theme-next">NexT.Pisces</a> v5.1.4</div>




        
<div class="busuanzi-count">
  <script async src="https://dn-lbstatics.qbox.me/busuanzi/2.3/busuanzi.pure.mini.js"></script>

  
    <span class="site-uv">
      <i class="fa fa-user">本站访客数</i>
      <span class="busuanzi-value" id="busuanzi_value_site_uv"></span>
      人数
    </span>
  

  
    <span class="site-pv">
      <i class="fa fa-eye">本站总访问量</i>
      <span class="busuanzi-value" id="busuanzi_value_site_pv"></span>
      次
    </span>
  
</div>





  <script type="text/javascript">
    (function() {
      var hm = document.createElement("script");
      hm.src = "//tajs.qq.com/stats?sId=65946112";
      var s = document.getElementsByTagName("script")[0];
      s.parentNode.insertBefore(hm, s);
    })();
  </script>




        
      </div>
    </footer>

    
      <div class="back-to-top">
        <i class="fa fa-arrow-up"></i>
        
      </div>
    

    
      <div id="needsharebutton-float">
        <span class="btn">
          <i class="fa fa-share-alt" aria-hidden="true"></i>
        </span>
      </div>
    

  </div>

  

<script type="text/javascript">
  if (Object.prototype.toString.call(window.Promise) !== '[object Function]') {
    window.Promise = null;
  }
</script>









  












  
  
    <script type="text/javascript" src="/lib/jquery/index.js?v=2.1.3"></script>
  

  
  
    <script type="text/javascript" src="/lib/fastclick/lib/fastclick.min.js?v=1.0.6"></script>
  

  
  
    <script type="text/javascript" src="/lib/jquery_lazyload/jquery.lazyload.js?v=1.9.7"></script>
  

  
  
    <script type="text/javascript" src="/lib/velocity/velocity.min.js?v=1.2.1"></script>
  

  
  
    <script type="text/javascript" src="/lib/velocity/velocity.ui.min.js?v=1.2.1"></script>
  

  
  
    <script type="text/javascript" src="/lib/fancybox/source/jquery.fancybox.pack.js?v=2.1.5"></script>
  


  


  <script type="text/javascript" src="/js/src/utils.js?v=5.1.4"></script>

  <script type="text/javascript" src="/js/src/motion.js?v=5.1.4"></script>



  
  


  <script type="text/javascript" src="/js/src/affix.js?v=5.1.4"></script>

  <script type="text/javascript" src="/js/src/schemes/pisces.js?v=5.1.4"></script>



  
  <script type="text/javascript" src="/js/src/scrollspy.js?v=5.1.4"></script>
<script type="text/javascript" src="/js/src/post-details.js?v=5.1.4"></script>



  


  <script type="text/javascript" src="/js/src/bootstrap.js?v=5.1.4"></script>



  


  




	





  





  












  





  

  
  <script src="https://cdn1.lncld.net/static/js/av-core-mini-0.6.4.js"></script>
  <script>AV.initialize("ohqkXUU5txOlX3MjJPCrw7jG-gzGzoHsz", "bPQhLzRSEsTqdIKAGRcHLhyN");</script>
  <script>
    function showTime(Counter) {
      var query = new AV.Query(Counter);
      var entries = [];
      var $visitors = $(".leancloud_visitors");

      $visitors.each(function () {
        entries.push( $(this).attr("id").trim() );
      });

      query.containedIn('url', entries);
      query.find()
        .done(function (results) {
          var COUNT_CONTAINER_REF = '.leancloud-visitors-count';

          if (results.length === 0) {
            $visitors.find(COUNT_CONTAINER_REF).text(0);
            return;
          }

          for (var i = 0; i < results.length; i++) {
            var item = results[i];
            var url = item.get('url');
            var time = item.get('time');
            var element = document.getElementById(url);

            $(element).find(COUNT_CONTAINER_REF).text(time);
          }
          for(var i = 0; i < entries.length; i++) {
            var url = entries[i];
            var element = document.getElementById(url);
            var countSpan = $(element).find(COUNT_CONTAINER_REF);
            if( countSpan.text() == '') {
              countSpan.text(0);
            }
          }
        })
        .fail(function (object, error) {
          console.log("Error: " + error.code + " " + error.message);
        });
    }

    function addCount(Counter) {
      var $visitors = $(".leancloud_visitors");
      var url = $visitors.attr('id').trim();
      var title = $visitors.attr('data-flag-title').trim();
      var query = new AV.Query(Counter);

      query.equalTo("url", url);
      query.find({
        success: function(results) {
          if (results.length > 0) {
            var counter = results[0];
            counter.fetchWhenSave(true);
            counter.increment("time");
            counter.save(null, {
              success: function(counter) {
                var $element = $(document.getElementById(url));
                $element.find('.leancloud-visitors-count').text(counter.get('time'));
              },
              error: function(counter, error) {
                console.log('Failed to save Visitor num, with error message: ' + error.message);
              }
            });
          } else {
            var newcounter = new Counter();
            /* Set ACL */
            var acl = new AV.ACL();
            acl.setPublicReadAccess(true);
            acl.setPublicWriteAccess(true);
            newcounter.setACL(acl);
            /* End Set ACL */
            newcounter.set("title", title);
            newcounter.set("url", url);
            newcounter.set("time", 1);
            newcounter.save(null, {
              success: function(newcounter) {
                var $element = $(document.getElementById(url));
                $element.find('.leancloud-visitors-count').text(newcounter.get('time'));
              },
              error: function(newcounter, error) {
                console.log('Failed to create');
              }
            });
          }
        },
        error: function(error) {
          console.log('Error:' + error.code + " " + error.message);
        }
      });
    }

    $(function() {
      var Counter = AV.Object.extend("Counter");
      if ($('.leancloud_visitors').length == 1) {
        addCount(Counter);
      } else if ($('.post-title-link').length > 1) {
        showTime(Counter);
      }
    });
  </script>



  

  

  
  
  
  <link rel="stylesheet" href="/lib/needsharebutton/needsharebutton.css">

  
  
  <script src="/lib/needsharebutton/needsharebutton.js"></script>

  <script>
    
      pbOptions = {};
      
          pbOptions.iconStyle = "box";
      
          pbOptions.boxForm = "horizontal";
      
          pbOptions.position = "bottomCenter";
      
          pbOptions.networks = "Weibo,Wechat,Douban,QQZone,Twitter,Facebook";
      
      new needShareButton('#needsharebutton-postbottom', pbOptions);
    
    
      flOptions = {};
      
          flOptions.iconStyle = "box";
      
          flOptions.boxForm = "horizontal";
      
          flOptions.position = "middleRight";
      
          flOptions.networks = "Weibo,Wechat,Douban,QQZone,Twitter,Facebook";
      
      new needShareButton('#needsharebutton-float', flOptions);
    
  </script>

  

  
  
    <script type="text/x-mathjax-config">
      MathJax.Hub.Config({
        tex2jax: {
          inlineMath: [ ['$','$'], ["\\(","\\)"]  ],
          processEscapes: true,
          skipTags: ['script', 'noscript', 'style', 'textarea', 'pre', 'code']
        }
      });
    </script>

    <script type="text/x-mathjax-config">
      MathJax.Hub.Queue(function() {
        var all = MathJax.Hub.getAllJax(), i;
        for (i=0; i < all.length; i += 1) {
          all[i].SourceElement().parentNode.className += ' has-jax';
        }
      });
    </script>
    <script type="text/javascript" src="//cdn.mathjax.org/mathjax/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML"></script>
  


  

  

  <script type="text/javascript" src="/js/src/love.js"></script><!-- hexo-inject:begin --><!-- hexo-inject:end -->
</body>
</html>
